package carshow;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.net.SocketTimeoutException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Stack;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.quickserver.net.server.ClientCommandHandler;
import org.quickserver.net.server.ClientHandler;

/**
 * Handles the commands set from the IP network for accesses into the
 * SQLite database to this server (machine).  This works in conjunction
 * with the QuickServer library which provides the underlying network
 * interface logic.  This class implements the actual database connection
 * into SQLite by taking incoming request strings and, if necessary, reformatting
 * these into prepared statements or passing the string SQL request on
 * to the SQLite library for retrieval or insert/update/delete.
 * @author wmotycka
 */
public class SQLiteCommandHandler implements ClientCommandHandler {
    private Connection conn = null; // connection used by all methods
                                    // attempting to reuse connection
    private ClientHandler cHandler;
    /* debug enabled flag */
    private boolean debug = false;
    /** name of the carshow database disk file */
    public static final String DBfile = "carshow.db";
    private static final Object syncObject = new Object();
    /** name of the SQLite table used for storing the judging classes */
    public static final String JudgeClasses = "JudgeClasses";
    /** name of the SQLite table used to store the car show entrants */
    public static final String Entrants = "Entrants";
    /** name of the SQLite table used to store the car judging scores */
    public static final String Scoring = "Scoring";
    /** name of the SQLite table used for storing the clubs represented at the show */
    public static final String Clubs = "Clubs";
    /** name of the SQLite table used for storing a lookup table of award winners */
    public static final String Specials = "Specials";
    public static final String ShowData = "ShowData";
    private static String createEntrants = "create table "+
        Entrants + " (entrant_num integer primary key, fname varchar(45), "+
        "lname varchar(45), mnemonic varchar(10), address varchar(45), "+
        "city varchar(45), state varchar(8), zip varchar(12), "+
        "phone varchar(16), make varchar(45), model varchar(45), "+
        "color varchar(45), year integer, parking_loc varchar(10), "+
        "club varchar(45), preg integer, printed varchar(1), aw1 varchar(30), aw2 varchar(30) );";
    //private static String ENT_INIT_PSTMT = "insert into " + Entrants +
    //    " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
    //private static String deleteEntrant = "delete from "+Entrants+
    //    "Where entrant_num = ?;";
    //private static String updateEntrant = "update "+Entrants+
    //    "set ? = ? where entrant_num = ?;";
    
    private static String createJudgeClasses = "create table "+
        JudgeClasses + " (mnemonic varchar(10) primary key, "+
        "classname varchar(60), StartYear integer, EndYear integer);";
    private static String CLS_INIT_PSTMT = "insert into " + JudgeClasses +
        " values (?, ?, ?, ?);";
    //private static String deleteClass = "delete from "+JudgeClasses+
    //    " where cclass = ?;";

    //private static String updateScoringBase = "update "+Scoring+" set ";
    //private static String updateScoringJ1 = "update "+Scoring+
    //    "(Judge1, j1_cat1, j1_cat2, j1_cat3, j1_cat4, j1_cat5, j1_cat6)"+
    //    " values (?, ?, ?, ?, ?, ?, ?) where entrant_num = ?";
    //private static String updateScoringJ2 = "update "+Scoring+
    //    "(Judge2, j2_cat1, j2_cat2, j2_cat3, j2_cat4, j2_cat5, j2_cat6)"+
    //    " values (?, ?, ?, ?, ?, ?, ?) where entrant_num = ?";

    private static String createClubs = "create table "+
        Clubs + " (club varchar(45));";
    //private static String CLUB_INIT_PSTMT = "insert into " + Clubs +
    //    "values (?);";

    private static String createScoring = "create table "+
        Scoring + " (entrant_num integer primary key, Judge1 varchar(20), "+
        "Judge2 varchar(20), j1_cat1 integer, j1_cat2 integer, "+
        "j1_cat3 integer, j1_cat4 integer, j1_cat5 integer, "+
        "j1_cat6 integer, j1_total integer, j2_cat1 integer, "+
        "j2_cat2 integer, j2_cat3 integer, j2_cat4 integer, "+
        "j2_cat5 integer, j2_cat6 integer, j2_total integer);";
    //private static String SCOR_INIT_PSTMT = "insert into " + Scoring +
    //    " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    /*
     * The Specials table will be a lookup to map award to entrant_num
     * assigned, making the finding of these award winners faster, although
     * an exclusion using the sql statements will be used to make these
     * not visible in the Awards panel, or at least listed in a different way
     * on that panel to make them non-participating in the scoring for the
     * various classes (see requirements, if DTO ever publishes them...)
     * 
     * This also makes setting these award categories more general because
     * we could then pull the labels for each of the check boxes in the header
     * and make them variable depending upon a config file.  Thus making the
     * program more general in application for various carshows.
     * Any number of awards could be assigned with this architecture.
     */
    private static String createSpecials = "create table "+
        Specials + " (award varchar(30) primary key, entrant_num integer);";
    private static String createShowData = "create table "+ShowData+
        " (showname varchar, showdate varchar, location varchar, aux1 varchar)";
    /**
     * default constructor.
     */
    public SQLiteCommandHandler() {
    }
    /**
     * Executed when a new client connection is established
     * For our purposes this provides a simple 'ack' response
     * to the client that the machine is connected.
     * @param handler
     * @throws SocketTimeoutException
     * @throws IOException
     */
    public void gotConnected(ClientHandler handler)
        throws SocketTimeoutException, IOException {
	String addr = "cmdHandler.gotConnected: Address: "+handler.getHostAddress();
        if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO, 
            addr);
		//handler.sendClientMsg("Connection-Reply-Text-If-Desired");
    }
    /**
     * executed when a client connection is lost.  currently unused but
     * may form part of a handshake with the server if/when we change to
     * a different SQL server model.
     * @param handler
     * @throws IOException
     */
    public void lostConnection(ClientHandler handler)
	throws IOException {
	//handler.sendSystemMsg("Connection lost : " +
	//    handler.getSocket().getInetAddress());
    }
    /**
     * executed when a client connection is being closed.  currently
     * there are no tasks being performed here but that doesn't mean
     * we may not want to leverage this notification mechanism later.
     * @param handler
     * @throws IOException
     */
    public void closingConnection(ClientHandler handler)
	throws IOException {
	//handler.sendSystemMsg("Closing connection : " + 
	//    handler.getSocket().getInetAddress());
    }
    /**
     * THE SERVER SIDE QuickServer SQL REQUEST HANDLER
     * Executed when any command is received by the server
     * on the server port.  This forms essentially an event
     * handler, when a new client message is received this
     * method parses the message and performs the SQL request
     * passing the rows as Strings back to the client.
     * This is a required method of the QuickServer ClientCommandHandler
     * class.  The extensive use of sendClientMsg() leverages the QuickServer
     * native reply mechanism which plumbs the strings back down the TCP
     * pipe to the client.  Provided the SQLiteQS properly synchronizes access
     * to the DB, these calls should be single-lined and thread safe.
     * @param handler QuickServer client handler class of this connection
     *        In the CarShow app this (SQLiteCommandHandler) is the handler
     *        along with the base class QuickServer..ClientCommandHandler
     * @param command string from client with request for DB action
     */
    public void handleCommand(ClientHandler handler, String command)
            throws SocketTimeoutException, IOException { 
        bop(handler,command);
    }
    private synchronized void bop(ClientHandler handler, String command) 
            throws SocketTimeoutException, IOException {
        if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"handleCommand, command rcvd: "+command);
        this.cHandler = handler;
        boolean in_init = false;
        ResultSet rs = null;
        Statement stat = null;
        int exc_ret = -1;

        ResultSetMetaData rsmd = null;
	if(command.equals("Quit")) { // silently quit the client
	    //handler.sendClientMsg("Bye ;-)");
            //System.err.println("handleCommand: quit....");
	    Logger.getLogger(CarShowUI.class.getName()).log(
                Level.INFO, "handleCommand: quit....");
            handler.closeConnection();
	    try {
		conn.close();
	    } catch (SQLException e) {
		//System.err.println("handleCommand: close DB error: "+e.toString());
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.INFO, "handleCommand: close DB error: ",e);
	    }
	}
        else if (command.equals("ClassInit")) {
            initClassSet();
        }
        else if (command.equals("ScoringInit")) {
            init_scoring(true);
        }
        else if (command.equals("Awards Init")) {
            initSpecials();
        }
        else if (command.equals("Save")) {
            int ret = saveShow();
            handler.sendClientMsg(ret+"\n\rEOF");
        }
        else if (command.equals("Audit")) { // fix the DB
            in_init=true;
            try {
	        Class.forName("org.sqlite.JDBC");
	    }
	    catch (ClassNotFoundException cnfe) {
	        //throw new SQLException(cnfe.getCause());
	        //System.err.println("sqlite JDBC Class not found: "+cnfe.getMessage());
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: close DB error: ",cnfe);
	        handler.sendClientMsg("-4\n\rEOF");
                throw new IOException(cnfe);
	    }
	    // open the DB file
            if(conn == null) {
                try {
                    conn = DriverManager.getConnection("jdbc:sqlite:"+DBfile);
                }
                catch (SQLException e) {
		    //System.err.println("handleCommand: DB connection open failed "+
                    //        e.getMessage());
		    //e.printStackTrace(System.err);
                    Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: DB connection open failed ", e);
	            handler.sendClientMsg("-4\n\rEOF");
                    return;
                }
            }
            boolean have_ent = false, have_class = false, have_score = false;
	    boolean have_clubs = false, have_specials = false, have_show = false;
            try {
                if(conn != null) if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"Audit: conn: "+conn.toString());
                else {
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"Audit conn: conn == null may not be a problem...");
                }
		DatabaseMetaData md = conn.getMetaData();

                //boolean have_specials = false;
                synchronized(syncObject) {
                    rs = md.getTables(null, null, "%", null);

                    while (rs.next()) {
                        if(rs.getString(3).equals(Entrants)) have_ent = true;
                        else if (rs.getString(3).equals(JudgeClasses)) have_class = true;
                        else if (rs.getString(3).equals(Scoring)) have_score = true;
                        else if (rs.getString(3).equals(Clubs)) have_clubs = true;
                        else if (rs.getString(3).equals(Specials)) have_specials = true;
                        else if (rs.getString(3).equals(ShowData)) have_show = true;
                    }
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"have_ent:"+have_ent+" have_class:"+have_class+
                            " have_score:"+have_score+" have_clubs:"+have_clubs+
                            " have_specials: "+have_specials+" have_show: "+have_show);
                    rs.close();
                }
		if(!have_ent) init_entrants(in_init);
		if(!have_class) { 
                    init_classes();
                    initClassSet();
                }
		if(!have_score) init_scoring(in_init);
		if(!have_clubs) init_clubs(in_init);
                if(!have_specials) initSpecials();
                if(!have_show) initShow();
	        in_init=false;  // indicate end of init phase
            } catch (SQLException e) {
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: SQL audit init query failed ", e);
                handler.sendClientMsg("-3\n\rEOF");
                //System.err.println("SQL audit query failed "+e);
		//e.printStackTrace(System.err);
            }
            int ret = 0;
            if(!have_ent) ret++;
            if(!have_clubs) ret++;
            if(!have_class) ret++;
            if(!have_score) ret++;
            if(!have_specials) ret++;
            handler.sendClientMsg(ret+"\n\rEOF");
        }
        //----------------------------------------------------
        // SQL HANDLER BELOW - NOT ALL CMDS ARE TREATED EQUAL!
        //----------------------------------------------------
        else { // SQL Request/not a SQLiteQS builtin command
	    try {
	        Class.forName("org.sqlite.JDBC");
	    }
	    catch (ClassNotFoundException cnfe) {
	        Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: sqlite JDBC not found ", cnfe);
                handler.sendClientMsg("-4\n\rEOF");
	    }
            // open the DB file
            if(conn == null) {
                try {
                    conn = DriverManager.getConnection("jdbc:sqlite:"+DBfile);
                }
                catch (SQLException e) {
		    Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: Driver getConnection failed", e);
                    handler.sendClientMsg("-5\n\rEOF");
                }
            }
	    try {
		if(command.toLowerCase().startsWith("select")) {
                   if(debug) Logger.getLogger(CarShowUI.class.getName()).log(
                   Level.INFO, "handleCommand: query: "+command);
		   // select cases are reads no need to
		   // synchronize these
		    stat = conn.createStatement();
		    Stack<String> inStack = new Stack<String>();
                    // synchronize this block to keep result set stable
                    synchronized(syncObject) {
                        try {
                            rs = stat.executeQuery(command);
                        } catch (NullPointerException npe) {
                            Logger.getLogger(CarShowUI.class.getName()).log(
                                Level.SEVERE, "handleCommand: SQL exec query"+
                                " failed on query: "+command, npe);
                            //System.err.println("a null ptr exception on this query: "+
                            //        command);
                            //npe.printStackTrace(System.err);
                            handler.sendClientMsg("-5\n\rEOF");
                            return;
                        }
                        rsmd = rs.getMetaData();
                        int cols = rsmd.getColumnCount();
                        // format result set in to String array
                        // in same order as returned
                        while(rs.next()) {
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,rs.toString());
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"columns "+cols);
                            StringBuffer sb = new StringBuffer();
                            for (int k=1; k <= cols; k++) {
                                sb.append(rs.getString(k));
                                if(k < cols) sb.append(",");
                            }
                            inStack.push(sb.toString());
                        }
                        rs.close();
                    } // end synchronized block
                    int irow = inStack.size();
                    // reverse the order of the stack so the
                    // String array returned is in orig order
                    for(int j = irow-1; j >= 0; j--)
                        handler.sendClientMsg(inStack.pop()+"\n");
                    handler.sendClientMsg("EOF");
                }
                /*
                * sqlPut() INSERT SQL case
                */
                else if (command.toLowerCase().startsWith("insert")) {
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).
                        log(Level.INFO,"run: insert case");
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).
                        log(Level.INFO,"run: command: "+command);
                    HashMap<String,String> args = parseInsert(command);
                    int ret = setSqlString(command); // synchronized write done here...
                    if (ret <= 0)
                        handler.sendClientMsg(ret+"\n\rEOF");
                    else { // may need client request done
                        if(debug) Logger.getLogger(CarShowUI.class.getName()).
                            log(Level.INFO,"post setSQL ret: "+ret);
                        if(command.contains(Entrants)) {
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"requesting entrant number");
                            String entNumRequest = "select entrant_num from " +
                            Entrants+" where fname = "+args.get("fname")+
                            " and lname ="+args.get("lname")+" and address ="+
                            args.get("address");
                            synchronized(syncObject) {
                                stat = conn.createStatement();
                                if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                    log(Level.INFO,"cmdHdlr: getting entrant_num:"+
                                    entNumRequest);
                                rs = stat.executeQuery(entNumRequest);
                                rsmd = rs.getMetaData();
                                int cols = rsmd.getColumnCount();
                                String eNum = rs.getString("entrant_num");
                                int eNumInt = rs.getInt("entrant_num");
                                rs.close();
                                if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                    log(Level.INFO,"cmdHdlr: resultSet ent_num:"+eNum);
                                if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                    log(Level.INFO,"cmdHldr: resultSet entNumInt:"+eNumInt);
                                if(cols == 1) {
                                    String request = "insert into Scoring "+
                                        "(entrant_num, Judge1, Judge2, j1_total, j2_total) values ("+
                                        eNum+", \"\", \"\", 0, 0)";
                                    int rett = stat.executeUpdate(request);
                                    if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                        log(Level.INFO,"scoring table init on new entrant done...");
                                    handler.sendClientMsg(eNum+"\n\rEOF");
                                }
                                else handler.sendClientMsg("-1\n\rEOF");
                            }
                        }
                    /*  else if(command.contains(Clubs)) { // clubs insert
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"clubs insert hndlr ret="+ret);
                            handler.sendClientMsg(Integer.toString(ret)+"\n\rEOF");
                        }
                        else if(command.contains(JudgeClasses)) { // classes insert
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"judgeClasses insert hndlr ret="+ret);
                            handler.sendClientMsg(Integer.toString(ret)+"\n\rEOF");
                        }
                        else if(command.contains(Scoring)) { // Scoring insert
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"scoring insert hndlr ret="+ret);
                            handler.sendClientMsg(Integer.toString(ret)+"\n\rEOF");
                        }
                        else if (command.contains(Specials)) { // Specials insert
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"specials insert hndlr ret="+ret);
                            handler.sendClientMsg(Integer.toString(ret)+"\n\rEOF");
                        } */
                        else {
                            if(debug) Logger.getLogger(CarShowUI.class.getName()).
                                log(Level.INFO,"Insert operation return code ret="+ret);
                                handler.sendClientMsg(Integer.toString(ret)+"\n\rEOF"); 
                        }
                    }
                }
                else if (command.toLowerCase().startsWith("delete")) { // delete is non-atomic!!!
                    stat = conn.createStatement();
                    exc_ret = stat.executeUpdate(command);
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,
                        "ret from execUpdate: "+exc_ret);
                        handler.sendClientMsg(Integer.toString(exc_ret)+"\n\rEOF");
                }
                else { // Update - build a PreparedStatement b/c SQLite wont 
                       // update on String statements
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,
                        "cmdHdlr: not insert/select/delete: "+command);
                    String tableName = parseTableName(command);
                    HashMap upArgs = parseUpdate(command);
                    String[] whereArgs = null;
                    /*if(command.contains(Entrants) || command.contains(Scoring))  {
                        whereArgs = new String[2];
                        whereArgs[0] = "entrant_num";
                        whereArgs[1] = (String)upArgs.get("entrant_num");
                        //upArgs.remove("entrant_num");
                    }
                    else if (command.contains(Specials)) {
                        whereArgs = new String[2];
                        whereArgs[0] = "award";
                        whereArgs[1] = (String)upArgs.get("award");
                        //upArgs.remove("award");
                    }
                    else if (command.contains(ShowData)) {
                        whereArgs = new String[2];
                        whereArgs[0] = "showname";
                        whereArgs[1] = (String)upArgs.get("showname");
                    }
                    else { */
                        whereArgs = new String[2];
                        int idx1 = command.indexOf("where");
                        String last = command.substring(idx1);
                        last = last.substring(6);
                        String tok = last.substring(0, last.indexOf(" "));
                        whereArgs[0] = tok;
                        whereArgs[1] = (String)upArgs.get(tok);
                    //}
                    upArgs.remove(whereArgs[0]);
                    java.util.Set<String> keySet = upArgs.keySet();
                    StringBuffer sb = new StringBuffer();
                    sb.append("update ");
                    sb.append(tableName);
                    sb.append(" set ");
                    mkPrep(sb, upArgs);
                    sb.append(" where "+whereArgs[0]+" = ?");
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,
                        "updating using the prep stmt: "+sb.toString());
                    exc_ret = setSqlPrepared(sb, keySet, upArgs, whereArgs);                
                    handler.sendClientMsg(Integer.toString(exc_ret)+"\n\rEOF");
		}
            } catch (SQLException e) {
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: SQL exec query"+
                    " failed", e);
                handler.sendClientMsg("-8\n\rEOF");
            } catch (NullPointerException npe) {
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "handleCommand: execUpdate NullPtrException caught"+
                    " on update", npe);
		handler.sendClientMsg("-9\n\rEOF");
            }
	} // close else block (SQL command request case)
    }
    
    /**
     * parses the table name specified in the SQL statement and returns the
     * table name string.
     * @param command the SQL statement to parse for the table name
     * @return string representing the SQL table name to alter/select
     */
    public String parseTableName(String command) {
        String ttable = command.substring(7);
        int nextSpace = ttable.indexOf(" ");
        ttable = ttable.substring(0, nextSpace);
        return ttable;
    }
    /**
     * Make a prepared statement from the supplied HashMap values putting
     * the resulting prepared statement text into the StringBuffer specified.
     * This forms part of the update DB operation of the CommandHandler.
     * Updates use this code to make the prepared statement preamble
     * to match column name to value (? = ?) for each supplied column name
     * in the sql statement originally parsed into upArgs.  Typically the
     * string buffer is already initialized to contain the update into TableName
     * portion of the prepared statement, this only adds the column clauses.
     * @param sb StringBuffer destination of the prepared statement text
     * @param updArgs HashMap of the DB table columns to be updated
     */
    private void mkPrep(StringBuffer sb, HashMap updArgs) {
        java.util.Set<String> keySet = updArgs.keySet();
        java.util.List keyList = asSortedList(keySet);
        java.util.Iterator<String> setIter = keyList.iterator();
        while (setIter.hasNext()) {
            sb.append(setIter.next());
            sb.append(" = ? , ");
        }
        sb.deleteCharAt(sb.length()-2); // remove last , in sb
    }
    
    /**
     * asSortedList() will sort the Collection type class passed into 
     * a List object.  It accepts any collection data type although it
     * is primarily used for String objects in this application.  It
     * relies upon the objects to have some comparability in the List object
     * so using it on objects that don't provide something that can be used for
     * comparison will probably fail.
     * @param c the java.util.Collection() object containing the data that
     * are of type <T> 
     * @return a List object of type <T> 
     */
    private static <T extends Comparable<? super T>> java.util.List<T> asSortedList(java.util.Collection<T> c) {
        java.util.List<T> list = new java.util.ArrayList<T>(c);
        java.util.Collections.sort(list);
        return list;
    }

    /**
     * Method access for synchronized DB table Update operations.  This allows
     * the setSQL() synchronized method to be used by both updates and inserts
     * through these wrapping formatter methods.
     * @param sb StringBuffer containing the PreparedStatement text
     * @param keySet Set of keys into upArgs of passed update column names
     * @param upArgs HashMap of key=>value pairs for updated columns
     * @param whereArgs String array of arguments in where clause of update
     * @return success/fail state of setSQL() operation performed
     */
    private int setSqlPrepared(StringBuffer sb, 
            java.util.Set<String> keySet, HashMap upArgs, String[] whereArgs) {
        return setSQL(sb, keySet, upArgs, whereArgs);
    }
    /**
     * Method access for synchronized DB table Insert operations.
     * @param s SQL command string to be sent
     * @return success/fail state of setSQL() operation performed
     */
    private int setSqlString(String s) {
        return setSQL(s, null, null, null);
    }
    /**
     * Handles all mutable operations performed on tables (insert,
     * delete, update.)  Apparently all three are used in executeUpdate()
     * and return an integer reply from SQLite.  Queries are cheap
     * so those are done in-line up in the commandHandler
     * @param command
     * @return
     */
    private synchronized int setSQL(Object command, java.util.Set<String> keySet,
            HashMap upArgs, String[] whereArgs) {
        int retval = -1;
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                "setSQL: Sqlite JDBC class not found", ex);
            return -4;
        }
        try {
            if(conn == null)
                conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);
        }
        catch (SQLException sqle) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,
                "setSQL: Sqlite getConnection failed", sqle);
            return -5;
        }
        if(keySet == null ) { // proxy for if(command instanceof String) but faster
            retval = -1;
            if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"setSQL: setSQL: executeUpdate on "+command);
            //int ret = -1;
            Statement stat;
            try {
                stat = conn.createStatement();
            } catch (SQLException sqle) {
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,"setSQL: createStmt failed wiht SQLE");
                sqle.printStackTrace(System.err);
                return -1;
            }
            try {
                retval = stat.executeUpdate((String)command);
            } catch (SQLException sqle) {
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,"setSQL: sql exception on command:"+command);
                sqle.printStackTrace(System.err);
                // return a negative return code to indicate a problem
                retval = -1;
            } catch (NullPointerException npe) {
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,"setSQL: NPE on stmt.execUpdate() command:"+command);
                retval = -1;
            }
            if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,"setSQL: execUpdate ret:"+retval);
        }
        else { // prepared statement
            try {
                String lsb = ((StringBuffer)command).toString();
                PreparedStatement prep = conn.prepareStatement(((StringBuffer)command).toString());
                java.util.List<String> keyList = asSortedList(keySet);
                java.util.Iterator<String> setIter = keyList.iterator();
                int fnum = 1;
                while(setIter.hasNext()) {
                    String currKey = setIter.next();
                    if(((String)upArgs.get(currKey)).contains("\"")) {
                        String tVal = (String)upArgs.get(currKey);
                        tVal = tVal.replaceAll("\"","");
                        prep.setString(fnum, tVal);
                    }
                    else // (fnum > 0 && fnum < 12) || (fnum > 12 && fnum < 14)
                        prep.setInt(fnum, Integer.parseInt((String)upArgs.get(currKey)));
                    fnum++;
                }
                if(lsb.contains(Entrants) || lsb.contains(Scoring)) 
                    prep.setInt(fnum, Integer.parseInt(whereArgs[1]));
                //else if(lsb.contains(Specials)) prep.setString(fnum, whereArgs[0]);
                else prep.setString(fnum, whereArgs[1]);
                prep.addBatch();

                conn.setAutoCommit(false);
                int[] eret;
                eret = prep.executeBatch();
                retval = eret[0];
                conn.setAutoCommit(true);
                prep.close();
            } catch (SQLException sqle) {
                retval = -1;
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,
                    "SQL Exception on prepared statement"+
                    ((java.sql.PreparedStatement)command).toString(), sqle);
                //sqle.printStackTrace(System.err);
            }
        }
        return retval;
    }

    /**
     * initialize the Entrants table in the carshow.db SQLite database
     * @param init indicates if this is an init, if so then it puts in
     * a default Entrant for testing purposes.  Otherwise, it merely creates
     * the Entrants table with the correct schema.
     */
    private void init_entrants(boolean init) {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO, null, ex);
        }
        try {
        	if(conn == null || conn.isClosed())
        		conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);
            //
            Statement stat = conn.createStatement();
            if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"init_entrants: stat:"+stat.toString());
            stat.executeUpdate("drop table if exists " + Entrants + ";");
            stat.executeUpdate(createEntrants);
        } catch (SQLException ex) {
            //System.err.println("SQL exception on Entrant table create: "+ex.getMessage());
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE,
                "SQL exception on Entrant table create: "+ex.getMessage(), ex);
            //ex.printStackTrace(System.err);
        }
    }
    /**
     * Initialize the car judging classes (JudgeClasses) table.
     */
    private void init_classes() {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace(System.err);
            return;
        }
        try {
            if (conn == null || conn.isClosed())
            	conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);
            Statement stat = conn.createStatement();
            stat.executeUpdate("drop table if exists " + JudgeClasses + ";");
            if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"init_classes: stat: "+stat.toString());
            stat.executeUpdate(createJudgeClasses);
        } catch (SQLException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                "SQLException on classes create: "+ex.getMessage(), ex);
        }
    }

    /**
     * This initializes the set of car classes to be judged.  This first
     * version hard codes the class data, a subsequent version will allow
     * using an initialization file containing a CSV formatted classes table.
     * The JudgeClasses table is populated with the current year for those
     * classes that are "to present" meaning the later model groups.
     */
    private void initClassSet() {
        GregorianCalendar gc = new GregorianCalendar();
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if (conn == null || conn.isClosed())
            	conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);
            DatabaseMetaData md = conn.getMetaData();
            PreparedStatement prep = conn.prepareStatement(CLS_INIT_PSTMT);
            prep.setString(1, "PRE");
            prep.setString(2, "Pre Registration Class");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            //
            // Classes below are for the REBELS Car Club show and may not
            // reflect your club's scoring rules.  Adjust accordingly or wait
            // for the version that uses an initialization file
            //
            prep.setString(1, "A");
            prep.setString(2, "Street Rods - 1931 & OLDER");
            prep.setInt(3, 1886);
            prep.setInt(4, 1931);
            prep.addBatch();
            prep.setString(1, "B");
            prep.setString(2, "Street Rods - 1932 to 1938");
            prep.setInt(3, 1932);
            prep.setInt(4, 1938);
            prep.addBatch();
            prep.setString(1, "C");
            prep.setString(2, "Street Rods - 1939 to 1948");
            prep.setInt(3, 1939);
            prep.setInt(4, 1948);
            prep.addBatch();
            prep.setString(1, "D");
            prep.setString(2, "Cars Stock - 1931 & OLDER");
            prep.setInt(3, 1886);
            prep.setInt(4, 1931);
            prep.addBatch();
            prep.setString(1, "E");
            prep.setString(2, "Cars Stock - 1932 to 1938");
            prep.setInt(3, 1932);
            prep.setInt(4, 1938);
            prep.addBatch();
            prep.setString(1, "F");
            prep.setString(2, "Cars Stock - 1939 to 1948");
            prep.setInt(3, 1939);
            prep.setInt(4, 1948);
            prep.addBatch();
            prep.setString(1, "G");
            prep.setString(2, "Cars Stock - 1949 to 1960");
            prep.setInt(3, 1949);
            prep.setInt(4, 1960);
            prep.addBatch();
            prep.setString(1, "H");
            prep.setString(2, "Cars Stock - 1961 to 1967");
            prep.setInt(3, 1961);
            prep.setInt(4, 1967);
            prep.addBatch();
            prep.setString(1, "I");
            prep.setString(2, "Cars Stock - 1968 to 1972");
            prep.setInt(3, 1968);
            prep.setInt(4, 1972);
            prep.addBatch();
            prep.setString(1, "J");
            prep.setString(2, "Cars Stock - 1973 to 1989");
            prep.setInt(3, 1973);
            prep.setInt(4, 1989);
            prep.addBatch();
            prep.setString(1, "K");
            prep.setString(2, "Cars Stock - 1990 to Current");
            prep.setInt(3, 1990);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "L");
            prep.setString(2, "Cars Modified - 1949 to 1954");
            prep.setInt(3, 1949);
            prep.setInt(4, 1954);
            prep.addBatch();
            prep.setString(1, "M");
            prep.setString(2, "Cars Modified - 1955 to 1957");
            prep.setInt(3, 1955);
            prep.setInt(4, 1957);
            prep.addBatch();
            prep.setString(1, "N");
            prep.setString(2, "Cars Modified - 1958 to 1964");
            prep.setInt(3, 1958);
            prep.setInt(4, 1964);
            prep.addBatch();
            prep.setString(1, "O");
            prep.setString(2, "Cars Modified - 1965 to 1969");
            prep.setInt(3, 1965);
            prep.setInt(4, 1969);
            prep.addBatch();
            prep.setString(1, "P");
            prep.setString(2, "Cars Modified - 1970 to 1973");
            prep.setInt(3, 1970);
            prep.setInt(4, 1973);
            prep.addBatch();
            prep.setString(1, "Q");
            prep.setString(2, "Cars Modified - 1974 to Current");
            prep.setInt(3, 1974);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "R");
            prep.setString(2, "Trucks Stock - 1972 & Older");
            prep.setInt(3, 1886);
            prep.setInt(4, 1972);
            prep.addBatch();
            prep.setString(1, "S");
            prep.setString(2, "Trucks Stock - 1973 to Current");
            prep.setInt(3, 1973);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "T");
            prep.setString(2, "Trucks Modified - 1959 & Older");
            prep.setInt(3, 1886);
            prep.setInt(4, 1959);
            prep.addBatch();
            prep.setString(1, "U");
            prep.setString(2, "Trucks Modified - 1960 to 1972");
            prep.setInt(3, 1960);
            prep.setInt(4, 1972);
            prep.addBatch();
            prep.setString(1, "V");
            prep.setString(2, "Cars Modified - 1973 to Present");
            prep.setInt(3, 1973);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "W");
            prep.setString(2, "Unfinished Car & Truck Primed - All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "X");
            prep.setString(2, "Unfinished Car & Truck - All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "Y");
            prep.setString(2, "Other - 4X4 All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "Z");
            prep.setString(2, "Other - Rat Rods All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "IM");
            prep.setString(2, "Imports All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "YC");
            prep.setString(2, "Youth (16-20) Domestic Car - All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "YI");
            prep.setString(2, "Youth (16-20) - Youth Import All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            prep.setString(1, "YT");
            prep.setString(2, "Youth (16-20) - Youth Truck All Years");
            prep.setInt(3, 1886);
            prep.setInt(4, gc.get(Calendar.YEAR));
            prep.addBatch();
            
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);
            prep.close();

        } catch (SQLException ex) {
            System.err.println("SQLException on default class list create: "+ex.getMessage());
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Initialize the Scoring table.
     * @param init unused
     */
    private void init_scoring(boolean init) {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
            		"org.sqlite.JDBC class not found on classpath", ex);
        }
        try {
        	if (conn == null || conn.isClosed())
        		conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);
            //
            Statement stat = conn.createStatement();
            stat.executeUpdate("drop table if exists " + Scoring + ";");
            stat.executeUpdate(createScoring);
        } catch (SQLException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                "SQL Exception in SQLiteCommandHandler.init_scoring", ex);
            //ex.printStackTrace(System.err);
        }
    }
    
    /**
     * Initialize the Clubs table.  The Rebels are the hosting club,
     * so they are automatically entered.
     * In a future iteration the hosting club value will be taken from
     * the CarShowUI and used as the initial club placed into the DB.
     * @param in_init determines if the host club should be inserted at table creation
     */
    private void init_clubs(boolean in_init) {
        try {
        	Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if(conn == null || conn.isClosed())
        	conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);

            Statement stat = conn.createStatement();
            stat.executeUpdate("drop table if exists " + Clubs + ";");
            stat.executeUpdate(createClubs);
            if(in_init) {
        	stat.executeUpdate("insert into "+Clubs+" values (\"Rebels\");");
            }
        } catch (SQLException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                "SQLException on Clubs create: "+ex.getSQLState(), ex);
        }
    }

    /**
     * Initializes the Specials table where the special awards (non-scored)
     * are to be kept.
     */
    private void initSpecials() {
        try {
        	Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if(conn == null || conn.isClosed())
        	conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);

            Statement stat = conn.createStatement();
            stat.executeUpdate("drop table if exists " + Specials + ";");
            stat.executeUpdate(createSpecials);
        } catch (SQLException ex) {
            //System.err.println("SQLException on Clubs create: "+ex.getSQLState());
            Logger.getLogger(CarShowUI.class.getName()).log(
                Level.SEVERE, "SQLException on Clubs create: "+ex.getSQLState(), ex);
        }
    }
    
    /**
     * initialize the ShowData table where Administration Panel data is kept.
     */
    private void initShow() {
        try {
        	Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if(conn == null || conn.isClosed())
        	conn = DriverManager.getConnection("jdbc:sqlite:" + DBfile);

            Statement stat = conn.createStatement();
            stat.executeUpdate("drop table if exists " + ShowData + ";");
            stat.executeUpdate(createShowData);
        } catch (SQLException ex) {
            //System.err.println("SQLException on Clubs create: "+ex.getSQLState());
            Logger.getLogger(CarShowUI.class.getName()).log(
                Level.SEVERE, "SQLException on Clubs create: "+ex.getSQLState(), ex);
        }
    }
    /**
     * Save the SQL database to files as CSV data.
     */
    private int saveShow() {
        ResultSet rs = null;
        try {
	        Class.forName("org.sqlite.JDBC");
	}
	catch (ClassNotFoundException cnfe) {
	        //throw new SQLException(cnfe.getCause());
	        //System.err.println("saveShow: sqlite JDBC Class not found!");
                //cnfe.printStackTrace(System.err);
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "saveShow: sqlite JDBC Class not found!", cnfe);
                return -1;
	}
        // open the DB file
        if(conn == null) {
            try {
                conn = DriverManager.getConnection("jdbc:sqlite:"+DBfile);
            }
            catch (SQLException e) {
                //System.err.println("handleCommand: DB connection open failed "+
                //        e.getMessage());
                //e.printStackTrace(System.err);
                Logger.getLogger(CarShowUI.class.getName()).log(
                    Level.SEVERE, "saveShow: sqlite JDBC Class not found!", e);
                return -2;
            }
        }
        boolean have_ent = false, have_class = false, have_score = false;
        boolean have_clubs = false, have_specials = false;
        try {
            if(conn != null) if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"Audit: conn: "+conn.toString());
            else {
                if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"Audit conn: conn == null typically seen and not a problem...");
            }
            DatabaseMetaData md = conn.getMetaData();

            //boolean have_specials = false;
            synchronized(syncObject) {
                rs = md.getTables(null, null, "%", null);

                while (rs.next()) {
                    if(rs.getString(3).equals(Entrants)) have_ent = true;
                    else if (rs.getString(3).equals(JudgeClasses)) have_class = true;
                    else if (rs.getString(3).equals(Scoring)) have_score = true;
                    else if (rs.getString(3).equals(Clubs)) have_clubs = true;
                    else if (rs.getString(3).equals(Specials)) have_specials = true;
                }
                if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"saveShow: have_ent:"+have_ent+" have_class:"+have_class+
                    " have_score:"+have_score+" have_clubs:"+have_clubs+" have_specials:"+have_specials);
            }
        } catch (SQLException e) {
            //System.err.println("SQL audit query failed "+e);
            //e.printStackTrace(System.err);
            Logger.getLogger(CarShowUI.class.getName()).log(
                Level.SEVERE, "SQL audit query failed w/SQLException", e);
        }
        int ret = 0;
        if(have_ent) ret++;
        if(have_clubs) ret++;
        if(have_class) ret++;
        if(have_score) ret++;
        if(have_specials) ret++;
        if(ret < 5) if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"saveShow: database short a table, save may be bad");
        // table we want to save
        String[] tables = {Entrants, Scoring, Clubs};
        for(String tableName : tables) {
            try {
                File dFile = new File(tableName+".csv");
                FileWriter fw = new FileWriter(dFile);
                BufferedWriter bw = new BufferedWriter(fw);
                Statement s = conn.createStatement();
                rs = s.executeQuery("select * from "+tableName);
                ResultSetMetaData rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();
                while(rs.next()) {
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,rs.toString());
                    if(debug) Logger.getLogger(CarShowUI.class.getName()).log(Level.INFO,"columns "+cols);
                    StringBuffer sb = new StringBuffer();
                    for (int k=1; k <= cols; k++) {
                        sb.append(rs.getString(k));
                        if(k < cols) sb.append(",");
                    }
                    bw.write(sb.toString());
                    bw.newLine();
                }
                bw.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                    "SQL exception on Entrant data save", ex);
                return -1;
            } catch (java.io.IOException ioe) {
                Logger.getLogger(CarShowUI.class.getName()).log(Level.SEVERE, 
                    "IO exception on Entrant data save", ioe);
                return -2;
            }
        }
        return 0;
    }
    
    /**
     * Parse an update Entrants command for the field names & values
     * @return HashMap of fields->values pairs of table columns to update
     * 
     * the update command string is like this:
     * 
     * update Entrants set fname = "Joe", lname = "Smith", mnemonic = "AA", 
     * address = "111 Evergreen", city = "Someplace", state = "IL", 
     * zip = "60502", phone = "402-245-1245", make = "Cadillac", 
     * model = "DeVille", color = "Black", year = 1971, parking_loc ="D0001", 
     * club = "Cruisers", preg = 1 where entrant_num = 1
     */
    private HashMap<String,String> parseUpdate(String command) {
        HashMap<String,String> fieldSet = new HashMap<String,String>();
        int fieldsStart = -1;
        int fieldsEnd = -1;
        int valuesStart = -1;
        int setKeywd = command.indexOf("set");
        String argPart = command.substring(setKeywd+4);
        String[] argsArray = argPart.split(", ");
        int last = argsArray.length-1;
        int whereClause = argsArray[last].indexOf(" where ");
        // postWhere has entrant_num = # portion of where clause
        String postWhere = argsArray[last].substring(whereClause+7);
        String[] entField = postWhere.split(" = ");
        String lastSet = argsArray[last].substring(0, whereClause);
        argsArray[last] = lastSet;
        for (int i = 0; i < argsArray.length; i++) {
            int eqLoc = argsArray[i].indexOf(" = ");

            String key = argsArray[i].substring(0, eqLoc).trim();
            String val = argsArray[i].substring(eqLoc+2).trim();
            fieldSet.put(key, val);
        }
        fieldSet.put(entField[0], entField[1]);
        return fieldSet;
    }
    
    /**
     * Parses an insert SQL statement.
     * @param command
     * @return HashMap of key->value pairs for the table columns to insert
     */
    private HashMap<String,String> parseInsert(String command) {
    	HashMap<String,String> fieldSet = new HashMap<String,String>();
    	int fieldsStart = -1;
    	int fieldsEnd = -1;
    	int valuesStart = -1;
    	fieldsStart = command.indexOf("(");
    	fieldsEnd = command.indexOf(")");
    	valuesStart = command.indexOf("(", fieldsEnd+1);
    	String fieldNames = command.substring(fieldsStart+1, fieldsEnd);
        String values;
        if(command.indexOf(";") > 0)
            values = command.substring(valuesStart+1, command.length()-2);
        else
            values = command.substring(valuesStart+1, command.length()-1);
        String nfn = fieldNames.replaceAll("[ ]*,[ ]*", ",");
        fieldNames = nfn;
        String nv = values.replaceAll("[ ]*,[ ]*",",");
        values = nv;
    	String[] flds = fieldNames.split(",");
    	String[] vals = values.split(",");
    	for (int i = 0; i < flds.length; i++) {
    	    fieldSet.put(flds[i], vals[i]);
    	}
    	return fieldSet;
    }
    /**
     * set the client handler.  This is currently not used but makes the code
     * analyzers (SONAR, etc.) happy.  The client handler is an instance of
     * SqlClientReader() in normal use of the CarShow application.
     * @param cl the new client handler object
     */
    public void setHandler(ClientHandler cl) {
        cHandler = cl;
    }
    /**
     * returns the current client handler.  This is currently not used.
     * @return The active client handler object
     */
    public ClientHandler getHandler() {
        return cHandler;
    }
}
